package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Role;
import com.hnl.util.FormatUtil;

public class DBRoleDAO implements RoleDAO {
	public static final Logger LOG = LoggerFactory.getLogger(DBRoleDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadByRoleIDStmt;
	private PreparedStatement loadByUserIDStmt;
	private PreparedStatement updateStmt;
	private PreparedStatement delStmt;

	protected DBRoleDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public Role saveRole(Role role) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO ROLE (role_name, create_date, create_user, modify_date, modify_user) "
									+ "VALUES (?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, role.getRoleName());
			saveStmt.setString(2, FormatUtil.getXmlDate(role.getCreateDate()));
			saveStmt.setInt(
					3,
					role.getCreateUserID() == null ? Integer.valueOf(1) : role
							.getCreateUserID());
			saveStmt.setString(4, FormatUtil.getXmlDate(role.getModifyDate()));
			saveStmt.setInt(
					5,
					role.getModifyUserID() == null ? Integer.valueOf(1) : role
							.getModifyUserID());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				role.setRoleID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return role;
	}

	public Role loadRoleByRoleID(Integer roleID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Role role = null;
		try {
			loadByRoleIDStmt = conn
					.prepareStatement("SELECT role_ID, role_name, create_date, create_user, modify_date, modify_user FROM ROLE "
							+ "WHERE role_ID = ?");
			loadByRoleIDStmt.setInt(1, roleID);
			ResultSet rs = loadByRoleIDStmt.executeQuery();
			if (rs.next()) {
				role = new Role();
				role.setRoleID(rs.getInt("role_ID"));
				role.setRoleName(rs.getInt("role_name"));
				role.setCreateDate(rs.getDate("create_date"));
				role.setCreateUserID(rs.getInt("create_user"));
				role.setModifyDate(rs.getDate("modify_date"));
				role.setModifyUserID(rs.getInt("modify_user"));
			}
			rs.close();
			loadByRoleIDStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Role.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return role;
	}

	public Role loadRoleByRoleName(Integer roleName) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Role role = null;
		try {
			loadByRoleIDStmt = conn
					.prepareStatement("SELECT role_ID, role_name, create_date, create_user, modify_date, modify_user FROM ROLE "
							+ "WHERE role_name = ?");
			loadByRoleIDStmt.setInt(1, roleName);
			ResultSet rs = loadByRoleIDStmt.executeQuery();
			if (rs.next()) {
				role = new Role();
				role.setRoleID(rs.getInt("role_ID"));
				role.setRoleName(rs.getInt("role_name"));
				role.setCreateDate(rs.getDate("create_date"));
				role.setCreateUserID(rs.getInt("create_user"));
				role.setModifyDate(rs.getDate("modify_date"));
				role.setModifyUserID(rs.getInt("modify_user"));
			}
			rs.close();
			loadByRoleIDStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Role.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return role;
	}

	public void updateRole(Role role) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateStmt = conn
					.prepareStatement("UPDATE ROLE SET role_name=?, create_date=?, create_user=?, modify_date=?, modify_user=?"
							+ "WHERE role_ID=?");
			updateStmt.setInt(1, role.getRoleName());
			updateStmt
					.setString(2, FormatUtil.getXmlDate(role.getCreateDate()));
			updateStmt.setInt(
					3,
					role.getCreateUserID() == null ? Integer.valueOf(1) : role
							.getCreateUserID());
			updateStmt
					.setString(4, FormatUtil.getXmlDate(role.getModifyDate()));
			updateStmt.setInt(
					5,
					role.getModifyUserID() == null ? Integer.valueOf(1) : role
							.getModifyUserID());
			updateStmt.setInt(6, role.getRoleID());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error updating Role.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public void deleteRole(Integer roleID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			delStmt = conn.prepareStatement("DELETE FROM ROLE "
					+ "WHERE role_ID = ?");
			delStmt.setInt(1, roleID);
			delStmt.executeUpdate();
			delStmt.close();
		} catch (SQLException e) {
			LOG.error("Error deleting Role.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public Collection<Role> loadRolesByUserID(Integer userID) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<Role> roleCollection = new Vector<Role>();
		try {
			loadByUserIDStmt = conn
					.prepareStatement("SELECT role_ID, role_name, create_date, create_user, modify_date, modify_user FROM ROLE "
							+ "WHERE user_ID = ?");
			loadByUserIDStmt.setInt(1, userID);
			ResultSet rs = loadByUserIDStmt.executeQuery();
			while (rs.next()) {
				Role role = new Role();
				role.setRoleID(rs.getInt("role_ID"));
				role.setRoleName(rs.getInt("role_name"));
				role.setCreateDate(rs.getDate("create_date"));
				role.setCreateUserID(rs.getInt("create_user"));
				role.setModifyDate(rs.getDate("modify_date"));
				role.setModifyUserID(rs.getInt("modify_user"));
				roleCollection.add(role);
			}
			rs.close();
			loadByRoleIDStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading Role.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return roleCollection;
	}

}
